import requests,bs4
from bs4 import BeautifulSoup
import xlsxwriter
def getHTMLText(url):
    try:
        r=requests.get(url)
        r.raise_for_status()
        r.encoding=r.apparent_encoding
        return r.text
    except:
        raise
        print("request failed")

def getObjectData(soup):
    li=[]
    for tr in soup.find('tbody').children:
        if isinstance(tr,bs4.element.Tag):
            # # 解析出tr标签中的td标签后，将其储存在列表tds中
            tds=tr('td')
            li.append([tds[0].text,tds[1].text,tds[2].text,tds[4].text])
    return li
def printUnivList(result,num):
    print("排名"+"\t"+"\t"+"学校"+"\t"+"\t"+"城市"+"\t"+"\t"+"总分")
    list = []
    for i in range(num):
        #   去空格
        result1 = [x.strip() for x in result[i] if x.strip() != '']
        # 数据放进list中 或元组
        list.append(result1)
    createExecl(list)
def createExecl(list):
    print(list)
    # 创建一个Excel文件
    workbook = xlsxwriter.Workbook('d:\大学排名.xlsx')
    # 创建一个sheet
    worksheet = workbook.add_worksheet()
    title = [U'排名',U'学校',U'城市',U'总分']     #表格title
    worksheet.write_row('A1', title)  # title 写入Excel
    row = 1
    col = 0
    for i in range(len(list)):
         worksheet.write(row, col,list[i][0])
         worksheet.write(row, col+1, list[i][1])
         worksheet.write(row, col+2, list[i][2])
         worksheet.write(row, col+3, list[i][3])
         row += 1
    workbook.close()
if __name__ == '__main__':
    url="http://www.shanghairanking.cn/rankings/bcur/2020"
    demo = getHTMLText(url)
    soup = BeautifulSoup(demo,"html.parser")
    result = getObjectData(soup)
    printUnivList(result,len(result))







